package com.ratingbay.console.naivequery;

import java.util.*;
 
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;

import com.ratingbay.console.model.*;

public class TweetNaiveQuery{
	private TweetNaiveQuery(){
	}
	private static TweetNaiveQuery self = null;
    public static TweetNaiveQuery getInstance(){
    	if(null == self){
    		self = new TweetNaiveQuery();
    	}
        return self;
    }


    //used in class inner
    protected String getIdsStr(List<Long> idList){
        String idsStr = "(";
        Iterator<Long> it = idList.iterator();
        while(it.hasNext()){
            idsStr += it.next();
            if(it.hasNext()) idsStr += ",";
        }
        idsStr += ")";
        return idsStr;
    }
    
    public List<Long> findByIdGameAndUser(Long idGame, List<Long> tUserIds,int pageSize,int pageNum){
        EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        Long limit = (long)pageSize;
        int offset = pageNum*pageSize;
        String tUserIdsStr = getIdsStr(tUserIds);

        String sql = "select t.id from tweet t where t.id_game = " + idGame + " "
        		+ "and t.user_id in " + tUserIdsStr + " order by create_date desc limit " + limit + " offset " + offset;
        Query query =  em.createNativeQuery(sql);

        List<Long> objectArrayList = query.getResultList();
        em.close();
        return objectArrayList;
    }
    
    public List<Long> findIdGameByUser(Long tUserId,int pageNum,int pageSize){
        EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();
        
        int offset = pageNum*pageSize;
        String sql = "select id_game from tweet where user_id = " + tUserId + "group by id_game order by max(create_date) desc limit " + pageSize + " offset " + offset;
        Query query =  em.createNativeQuery(sql);

        List<Long> objectArrayList = query.getResultList();
        em.close();
        return objectArrayList;
    }
    
    public List<Long> findIdGameByUserIn(List<Long> tUserIds,int pageNum,int pageSize){
        EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();
        
        String tUserIdsStr = getIdsStr(tUserIds);
        int offset = pageNum*pageSize;

        String sql = "select  a.id_game from (select * from tweet t where t.user_id in " + tUserIdsStr + ""
        		+ " order by create_date desc limit 1000) a group by a.id_game order by max(create_date) desc limit " + pageSize + " offset " + offset;
        Query query =  em.createNativeQuery(sql);

        List<Long> objectArrayList = query.getResultList();
        em.close();
        return objectArrayList;
    }

    public Long findCountIdGameByUserIn(List<Long> tUserIds){
        EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String tUserIdsStr = getIdsStr(tUserIds);

        String sql = "select count(distinct t.id_game) from tweet t where t.user_id in " + tUserIdsStr;
        Query query =  em.createNativeQuery(sql);
        
        Long count = null;
        List<Long> objectArrayList = query.getResultList();
        for(int i=0;i<objectArrayList.size();i++) {
            count = objectArrayList.get(i);
            break;
        }
        em.close();
        return count;
    }

    public List<Object[]> findGamersByUserIds(List<Long> tUserIds){
        EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "";
        Iterator<Long> it = tUserIds.iterator();
        while(it.hasNext()){
            sql += "(select count(distinct t.id_game), t.user_id, u.username, u.user_profile_img_url from tweet t " +
            		"inner join twitter_user u on t.user_id=u.user_id where t.user_id = " + 
            it.next() + " group by t.user_id, u.user_profile_img_url, u.username limit 1)";
            if(it.hasNext()){
                sql += " UNION ";
            }
        }
        Query query =  em.createNativeQuery(sql);

        List<Object[]> objectArrayList = query.getResultList();
        em.close();
        return objectArrayList;
    }
}